package com.jing.spring_boot_demo.provider.rest.excel;

import com.jing.spring_boot_demo.provider.commons.entity.User;
import org.apache.poi.EmptyFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ImportExcel {

    public static void main(String[] args) {
        XSSFWorkbook workbook = null;
        XSSFSheet sheet = null;
        try {
            File file = new File("E:\\" + "导入测试.xlsx");
            if (!file.exists()){
                System.out.println("指定的文件不存在，自动创建该文件");
                file.createNewFile();
            }
            // 获取指定本地文件的输入流对象
            FileInputStream fis = new FileInputStream(file);
            // 根据本地输入流对象创建一个工作簿对象
            workbook = new XSSFWorkbook(fis);
        } catch (IOException e) {
            e.printStackTrace();
        }  catch (EmptyFileException e) {
            System.out.println("异常：" + e.getMessage());
            System.out.println("获取到了一个空的文件, 导入 excel 数据失败");
            return;
        }
        for(int i = 0;i < workbook.getNumberOfSheets();i++){
            //获取每个sheet
            sheet = workbook.getSheetAt(i);
            List<User> list = new ArrayList<>();
            //getPhysicalNumberOfRows获取有记录的行数
            for(int j = 1;j < sheet.getPhysicalNumberOfRows();j++){
                Row row = sheet.getRow(j);
                if(null!=row){
                    // getLastCellNum 获取最后一列
                    User user = new User();
                    for(int k = 0;k < row.getLastCellNum();k++){
                        if(null!=row.getCell(k)){
                            if(k==0){
                                Cell cell = row.getCell(0);
                                //cell->double
                                Double d = cell.getNumericCellValue();
                                //double->int
                                int id = new Double(d).intValue();
                                user.setId(id);
                            }
                            if(k==1){
                                Cell cell = row.getCell(1);
                                //cell->string
                                user.setName(cell.getStringCellValue().toString());
                            }
                            if(k==2){
                                Cell cell = row.getCell(2);
                                user.setPassword(cell.getStringCellValue().toString());
                            }
                            if(k==3){
                                Cell cell = row.getCell(3);
                                user.setRemark(cell.getStringCellValue().toString());
                            }
                        }

                    }
                    list.add(user);


                }

            }
            System.out.println("读取sheet表："+ workbook.getSheetName(i) + "完成");
            System.out.println(list);
        }
    }

    @Test
    void test() throws IOException {
        String path = "E:\\" + "导入测试.xlsx";
        InputStream resourceAsStream = this.getClass().getResourceAsStream(path);
        System.out.println(resourceAsStream);
        File file = new File("E:\\" + "导入测试.xlsx");
        FileInputStream fileInputStream = new FileInputStream(file);
        System.out.println(fileInputStream);
        XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
        System.out.println(sheets);
        for (int i = 0; i < sheets.getNumberOfSheets(); i++) {
            XSSFSheet sheetAt = sheets.getSheetAt(i);
            System.out.println(sheetAt);
            
        }
    }
}
